﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CentaLine.Infrastructure;
using CentaLine.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace CentaLine.DAL
{
    public class VitualPhoneDAL
    {
        Database db = DBHelper.CreateDataBase();//本地数据库
        Database dbRemote = DBHelper.CreateDataBase("CentalineConnectRemote");//供应商虚拟真实对应表
        Database dbEshopApply = DBHelper.CreateDataBase("CentalineV_EShopApplyConnect");//申报表

        #region 返回本地数据库虚拟电话列表数据
        /// <summary>
        /// 返回本地数据库虚拟电话列表数据
        /// </summary>
        /// <param name="directorId"></param>
        /// <returns></returns>
        public IList<VitualPhone> GetAllPhoneListDAL(string directorId = null, string vitualPhone = null)
        {
            var VitualPhoneWebInfo = GetVitualPhoneWebInfo();//获取虚拟电话类型


            List<VitualPhone> list = new List<VitualPhone>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT v.[Id],[VitualPhone],[DirectorName],[DirectorId],[AssigneSatus],[ModifyTime]");
            strSql.Append("  FROM [VTPhoneDB].[dbo].[VPMS.VitualPhone] v where 1=1");
            if (!string.IsNullOrEmpty(directorId))
                strSql.Append(" and [DirectorId]=@DirectorId");
            if (!string.IsNullOrEmpty(vitualPhone))
                strSql.Append(" and  v.[VitualPhone]=@vitalPhone");

            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "DirectorId", DbType.String, directorId);
            db.AddInParameter(cmd, "vitalPhone", DbType.String, vitualPhone);
            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBind(dataReader));
                }
            }
            if (VitualPhoneWebInfo.Any()) //远程数据表中 存在虚拟电话类型信息
            {
                //处理本地电话池的 电话类型
                for (int i = 0; i < list.Count; i++)
                {
                    var item = list[i];
                    var temp = VitualPhoneWebInfo.Find(w => item.VitualPhoneNum == w.VitualPhoneNum);
                    if (temp == null)
                    {
                        item.PhoneKind = new PhoneKind(-99, "无法获取该电话的类型");
                      // Logger.Log.Error("无法找到该虚拟号码对应的类型 " + item.VitualPhoneNum+",此号码可能已失效");
                    }
                    else
                    {
                        item.PhoneKind = temp.PhoneKind;
                    }
                }
            }
            return list;
        }
        #endregion

        #region 由于虚拟电话池存储本地,而网站类型需从供应商表中获取 虚拟电话对应的网站类型 表Calleeidvsstat 字段statusvalue
        public List<VitualPhone> GetVitualPhoneWebInfo(string vitualPhone = null)
        {
            var webKindEnum = GetPhoneKindList();
            List<VitualPhone> list = new List<VitualPhone>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [StatusValue],[calleeid]");
            strSql.Append("   FROM [calleeidvsstat] WHERE 1=1");
            if (!string.IsNullOrEmpty(vitualPhone))
                strSql.Append(" AND calleeid=@vitalPhone");
            DbCommand cmd = dbRemote.GetSqlStringCommand(strSql.ToString());
            dbRemote.AddInParameter(cmd, "vitalPhone", DbType.String, vitualPhone);
            using (IDataReader dataReader = dbRemote.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBindWebInfo(dataReader, webKindEnum));
                }
            }
            return list;
        }

        private VitualPhone ReaderBindWebInfo(IDataReader dataReader, List<PhoneKind> webKindEnum)
        {
            var kindId = Convert.ToInt32(dataReader["StatusValue"].ToString());
            var phoneKind = webKindEnum.Find(w => w.Id == kindId);
            if (phoneKind == null)
            {
                phoneKind = new PhoneKind(kindId, "未知待添加该类型");
            }
            var vp = new VitualPhone
            {
                VitualPhoneNum = dataReader["calleeid"].ToString(),
                PhoneKind = phoneKind
            };
            return vp;
        }
        #endregion

        #region 返回本地虚拟电话申请列表
        /// <summary>
        /// 返回虚拟电话申请列表
        /// </summary>
        /// <param name="applyMonth">必须参数，申请月份</param>
        /// <param name="directorId">区董工号</param>
        /// <returns></returns>
        public List<CounselorApplication> GetAllCounselorApplicationListDAL(DateTime applyMonth, string directorId = null, string counselorId = null, string status = null,string mobile=null)
        {
            List<CounselorApplication> list = new List<CounselorApplication>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT  
               v.[Id]
              ,[ApplyMonth]
              ,[User_Name]
              ,[User_Code]
              ,[Org_Name]
              ,[RC]
              ,[RCCode]
              ,k.id as 'PhoneKindId'
              ,Web_Name
              ,v.[Name]
              ,[Mobile]
              ,[Status]
              ,[ModifyDate]
              ,[VitualPhone]
              FROM [VTPhoneDB].[dbo].[VPMS.EShopApply] v join [VTPhoneDB].[dbo].[VPMS.PhoneKind] k on v.Web_Name=k.name ");
  
             strSql.Append("  WHERE [ApplyMonth]=@ApplyMonth");

            if (!string.IsNullOrEmpty(directorId))
                strSql.Append(" AND [RCCode]=@DirectorId");
            if (!string.IsNullOrEmpty(counselorId))
                strSql.Append(" AND [User_Code]=@counselorId");
            if (!string.IsNullOrEmpty(status))
                strSql.Append(" AND [status]=@status");
            if (!string.IsNullOrEmpty(mobile))
                strSql.Append(" AND [Mobile]=@Mobile");

            strSql.Append(" order by ModifyDate desc");
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "ApplyMonth", DbType.String, CommonHelper.DateForamt(applyMonth)); //转换成数据库可以识别的日期
            db.AddInParameter(cmd, "DirectorId", DbType.String, directorId);
            db.AddInParameter(cmd, "counselorId", DbType.String, counselorId);
            db.AddInParameter(cmd, "status", DbType.String, status);
            db.AddInParameter(cmd, "Mobile", DbType.String,mobile);

            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBindAPP(dataReader));
                }
            }
            return list;
        }
        #endregion

        #region 返回远程虚拟电话申请列表
       /// <summary>
        /// 返回远程虚拟电话申请列表
       /// </summary>
       /// <param name="applyMonth">申请月份</param>
       /// <returns></returns>
        public DataSet GetRmoteCounselorApplicationListDAL(DateTime applyMonth, string maxAsyncTime)
        {
            string filter = ConfigurationManager.AppSettings["HandlerWebsite"]; //只处理配置节中指定的网站
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	       [ApplyMonth] 
          ,[User_Name]
          ,[User_Code]
          ,[Org_Name]
          ,[RC]
          ,[RCCode]
          ,[Web_Name]
          ,[Name]
          ,[Mobile]
          ,[ModifyDate]
           FROM [V_EShopApply](nolock)");
           strSql.Append("  WHERE [applymonth]=@ApplyMonth"); //TODO   增量是根据修改时间来同步  本地表中最后一次同步时间
           if (!string.IsNullOrEmpty(maxAsyncTime))
           {
               strSql.Append(" and ModifyDate>@AsyncTime");
           }
           if (!string.IsNullOrEmpty(filter))
           {
               strSql.Append(" AND Web_Name  IN(" + filter + ")");
           }
           DbCommand cmd = dbEshopApply.GetSqlStringCommand(strSql.ToString());
           dbEshopApply.AddInParameter(cmd, "ApplyMonth", DbType.String, CommonHelper.DateForamt(applyMonth)); //转换成数据库可以识别的日期
           dbEshopApply.AddInParameter(cmd, "AsyncTime", DbType.String, maxAsyncTime); //转换成数据库可以识别的日期

           return dbEshopApply.ExecuteDataSet(cmd); 
        }
        #endregion

        #region  添加一条申请到本地库
        /// <summary>
        /// 添加一条申请到本地库
        /// </summary>
        /// <returns></returns>
        private bool AddCounselorApplication(DataRow dr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
              declare @status int=0
              declare @keyId int=-1
              if(exists(select top 1 [Id] from [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth and Web_Name=@Web_Name and Mobile=@Mobile and User_Code=@User_Code))
              begin
              set @status=-2
              end
              else if(exists(select top 1 [Id] from [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth and Web_Name=@Web_Name and User_Code=@User_Code and Mobile<>@Mobile ))
              begin
              select top 1 @keyId =[Id] from [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth and User_Code=@User_Code and Mobile<>@Mobile 
              set @status=-5
              update [VTPhoneDB].[dbo].[VPMS.EShopApply] set [Status]=-5 where [Id]=@keyId
              end
              else if(exists(select top 1 [Id] from [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth and User_Code<>@User_Code and Mobile=@Mobile ))
              begin
              select top 1 @keyId=[Id] from [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth and User_Code<>@User_Code and Mobile=@Mobile 
              set @status=-6
              update [VTPhoneDB].[dbo].[VPMS.EShopApply] set [Status]=-6 where [Id]=@keyId
              end
              insert into [VTPhoneDB].[dbo].[VPMS.EShopApply]
              ([ApplyMonth]
              ,[User_Name]
              ,[User_Code]
              ,[Org_Name]
              ,[RC]
              ,[RCCode]
              ,[Web_Name]
              ,[Name]
              ,[Mobile]
              ,[Status]
              ,[ModifyDate]
              ,[AsyncTime])");
            strSql.Append(" values (");
            strSql.Append("@ApplyMonth,@User_Name,@User_Code,@Org_Name,@RC,@RCCode,@Web_Name,@Name,@Mobile,@Status,@ModifyDate,@AsyncTime)");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ApplyMonth", DbType.String, dr["ApplyMonth"]);
            db.AddInParameter(dbCommand, "User_Name", DbType.String, dr["User_Name"]);
            db.AddInParameter(dbCommand, "User_Code", DbType.String, dr["User_Code"]);
            db.AddInParameter(dbCommand, "Org_Name", DbType.String, dr["Org_Name"]);
            db.AddInParameter(dbCommand, "RC", DbType.String, dr["RC"]);
            db.AddInParameter(dbCommand, "RCCode", DbType.String, dr["RCCode"]);
            db.AddInParameter(dbCommand, "Web_Name", DbType.String, dr["Web_Name"]);
            db.AddInParameter(dbCommand, "Name", DbType.String, dr["Name"]);
            db.AddInParameter(dbCommand, "Mobile", DbType.String, dr["Mobile"]);
            //dbRemote.AddInParameter(dbCommand, "Status", DbType.Int32, 0);//待处理
            db.AddInParameter(dbCommand, "ModifyDate", DbType.DateTime, dr["ModifyDate"]);
            db.AddInParameter(dbCommand, "AsyncTime", DbType.DateTime, dr["ModifyDate"]);
            int count = db.ExecuteNonQuery(dbCommand);
            return count == 1;
        }
        #endregion

        #region  添加一条申请到本地库
        /// <summary>
        /// 添加一条申请到本地库
        /// </summary>
        /// <returns></returns>
        public string AddCounselorApplication(CounselorApplication c)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
              declare @status int=0
              begin
              if(exists(select * from [VTPhoneDB].[dbo].[VPMS.EShopApply] where Web_Name=@Web_Name and Mobile=@Mobile and ApplyMonth=@ApplyMonth))
              set @status=-2
              else
              set @status=0
              end
              insert into [VTPhoneDB].[dbo].[VPMS.EShopApply]
              ([ApplyMonth]
              ,[User_Name]
              ,[User_Code]
              ,[Org_Name]
              ,[RC]
              ,[RCCode]
              ,[Web_Name]
              ,[Name]
              ,[Mobile]
              ,[Status]
              ,[ModifyDate])");
            strSql.Append(" values (");
            strSql.Append("@ApplyMonth,@User_Name,@User_Code,@Org_Name,@RC,@RCCode,@Web_Name,@Name,@Mobile,@Status,@ModifyDate)");
            strSql.Append("  select @@IDENTITY");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ApplyMonth", DbType.String, c.ApplicationMonth);
            db.AddInParameter(dbCommand, "User_Name", DbType.String, c.CounselorName);
            db.AddInParameter(dbCommand, "User_Code", DbType.String, c.CounselorId);
            db.AddInParameter(dbCommand, "Org_Name", DbType.String, c.Org_Name);
            db.AddInParameter(dbCommand, "RC", DbType.String, c.DirectorName);
            db.AddInParameter(dbCommand, "RCCode", DbType.String, c.DirectorId);
            db.AddInParameter(dbCommand, "Web_Name", DbType.String, c.PhoneKind.Name);
            db.AddInParameter(dbCommand, "Name", DbType.String, c.PhoneKind.Name);
            db.AddInParameter(dbCommand, "Mobile", DbType.String, c.Mobile);
            db.AddInParameter(dbCommand, "ModifyDate", DbType.DateTime, DateTime.Now);
            string  id = db.ExecuteScalar(dbCommand).ToString();
            return id;
        }
        #endregion
        
        #region 同步远程报表数据到本地
        /// <summary>
        /// 返回虚拟电话申请列表
        /// </summary>
        /// <param name="applyMonth">必须参数，申请月份</param>
        /// <param name="directorId">区董工号</param>
        /// <returns></returns>
        public bool AsyncCounselorApplicationListDAL(DateTime applyMonth)
        {
            bool res = true;
            try
            {
                string maxAsyncTime = GetMaxAsyncTime(applyMonth);//获取本地库中最后同步时间
                var ds = GetRmoteCounselorApplicationListDAL(applyMonth, maxAsyncTime); //TODO 此处要先检查是否有必要同步 重要！！
                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        AddCounselorApplication(dr);
                    }
                }
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("同步申报表到本地失败", e);
                res = false;
            }
            return res;
        }
        #endregion

        #region  获取本地当月申请表最后同步时间
        /// <summary>
        /// 获取本地当月申请表最后同步时间
        /// </summary>
        /// <param name="vitualPhone"></param>
        /// <returns></returns>
        public string GetMaxAsyncTime(DateTime appMonth)
        {
            string res = string.Empty;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT MAX(AsyncTime) asyncTime FROM [VTPhoneDB].[dbo].[VPMS.EShopApply] where ApplyMonth=@ApplyMonth ");
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "ApplyMonth", DbType.String, CommonHelper.DateForamt(appMonth));
            var obj = db.ExecuteScalar(cmd);
            if (!(obj == null || obj == DBNull.Value))
            {
                res = ((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss.fff");
            }
            return res;
        }
        #endregion

        #region  根据ID 返回申请信息
        /// <summary>
        /// 根据ID 返回申请信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public CounselorApplication GetCounselorApplicationByIdDAL(Int64 Id)
        {
            CounselorApplication model = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT  
               v.[Id]
              ,[ApplyMonth]
              ,[User_Name]
              ,[User_Code]
              ,[Org_Name]
              ,[RC]
              ,[RCCode]
              ,k.id as 'PhoneKindId'
              ,v.Web_Name
              ,v.[Name]
              ,[Mobile]
              ,[Status]
              ,[ModifyDate]
              ,VitualPhone
              FROM [VTPhoneDB].[dbo].[VPMS.EShopApply] v join [VTPhoneDB].[dbo].[VPMS.PhoneKind] k on v.Web_Name=k.name ");

            strSql.Append("  WHERE v.[Id]=@Id");
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "Id", DbType.Int64, Id);
            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                   model = ReaderBindAPP(dataReader);
                }
            }
            return model;
        }
        #endregion

        #region 数据持久化
        /// <summary>
        /// 数据持久化
        /// </summary>
        /// <param name="model"></param>
        /// <param name="applyMonth"></param>
        /// <returns></returns>
        public bool PersistenceCounselorApplicationDAL(CounselorApplication ca)
        {
            bool res = false;
            #region 更新区董电话号码分配状态 [VPMS.VitualPhone]- >[AssigneSatus] ="1"
            res = UpdateVitualPhoneAssignStatus(ca.VitualPhone);
            if (!res) return false;
            #endregion

            #region  更新申请表 状态为已成功处理 1 [VPMS.EShopApply] ->[Status]="1"
            res = UpdateEShopApp(ca);
            if (!res) return false;
            #endregion

            #region 添加数据到供应商
            res = AddTdialnumber(ca.VitualPhone);
            #endregion

            return res;
        }
        #endregion

        #region 更新申报表状态
        public bool UpdateEShopApp(CounselorApplication model)
        {
            try
            {
                StringBuilder strSqlUpdateEShopApply = new StringBuilder();
                strSqlUpdateEShopApply.Append(@"update  [VTPhoneDB].[dbo].[VPMS.EShopApply] set Status=@HandlerStatus,ModifyDate=GETDATE(),
                vitualPhone =@VitualPhone
                where Id=@EShopApplyId");
                DbCommand dbCommandUpdatestrSqlUpdateEShopApply = db.GetSqlStringCommand(strSqlUpdateEShopApply.ToString());
                db.AddInParameter(dbCommandUpdatestrSqlUpdateEShopApply, "EShopApplyId", DbType.String, model.Id);
                db.AddInParameter(dbCommandUpdatestrSqlUpdateEShopApply, "VitualPhone", DbType.String, model.VitualPhone== null ? "" : model.VitualPhone.VitualPhoneNum);
                db.AddInParameter(dbCommandUpdatestrSqlUpdateEShopApply, "HandlerStatus", DbType.String, (int)model.Status);
                int count = db.ExecuteNonQuery(dbCommandUpdatestrSqlUpdateEShopApply);
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("EShopApply更新失败" + model.VitualPhone, e);
                return false;
            }
        }
        #endregion

        #region 更新本地虚拟电话分配状态
        public bool UpdateVitualPhoneAssignStatus(VitualPhone vp)
        {
            try
            {
                StringBuilder strSqlUpdateVitualPhone = new StringBuilder();
                strSqlUpdateVitualPhone.Append(@"update [VTPhoneDB].[dbo].[VPMS.VitualPhone] set AssigneSatus=@AssigneSatus,[ModifyTime]= GETDATE() 
                where VitualPhone=@VitualPhone");
                DbCommand dbCommandUpdateVitualPhone = db.GetSqlStringCommand(strSqlUpdateVitualPhone.ToString());
                db.AddInParameter(dbCommandUpdateVitualPhone, "AssigneSatus", DbType.Int16, vp.AssignStatus);
                db.AddInParameter(dbCommandUpdateVitualPhone, "VitualPhone", DbType.String, vp.VitualPhoneNum);
                int count = db.ExecuteNonQuery(dbCommandUpdateVitualPhone);
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("VitualPhone更新失败" + vp.ToString(), e);
                return false;
            }
        }
        #endregion

        #region 删除供应商表中对应虚拟关系 供应商表
        /// <summary>
        /// 
        /// </summary>
        /// <param name="vitualPhone">虚拟号码</param>
        /// <param name="reason">删除原因</param>
        /// <returns></returns>
        public bool DeleteFromtdialnumber(string vitualPhone,string reason)
        {
            try
            {
                StringBuilder strSqlUpdateEShopApply = new StringBuilder();
                strSqlUpdateEShopApply.Append(@"delete FROM [tdialnumber] where calleeid=@VitualPhone");
                DbCommand cmd = dbRemote.GetSqlStringCommand(strSqlUpdateEShopApply.ToString());
                dbRemote.AddInParameter(cmd, "VitualPhone", DbType.String, vitualPhone);
                int count = dbRemote.ExecuteNonQuery(cmd);
                if (count == 1)
                {
                    Logger.Log.Info(reason);  
                }
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("tdialnumber表删除新失败", e);
                return false;
            }
        }
        #endregion

        #region 添加数据到供应商表 tdialnumber,这边暂不做其他操作，只是insert
        /// <summary>
        /// 添加数据到供应商表 tdialnumber,这边暂不做其他操作，只是insert
        /// </summary>
        /// <returns></returns>
        public bool AddTdialnumber(VitualPhone model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                //TODO 上线后更改成供应商正式数据表
                strSql.Append("insert into [tdialnumber](");
                strSql.Append("[dialnumber],[calleeid])");
                strSql.Append(" values (");
                strSql.Append("@ActulPhoneNum,@VitualPhone)");
                DbCommand dbCommand = dbRemote.GetSqlStringCommand(strSql.ToString());
                dbRemote.AddInParameter(dbCommand, "VitualPhone", DbType.String, model.VitualPhoneNum);
                dbRemote.AddInParameter(dbCommand, "ActulPhoneNum", DbType.String, model.ActulPhoneNum);
                int count = dbRemote.ExecuteNonQuery(dbCommand);
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("添加供应商表数据失败" + model, e);
                return false;
            }
        }
        #endregion

        #region 获取供应商数据库表 [tdialnumber] 表中 虚拟-真实号码 配对状态
        /// <summary>
        /// 获取供应商数据库表 [tdialnumber] 表中 虚拟-真实号码 配对状态
        /// </summary>
        /// <param name="actualNo">真实号码</param>
        /// <param name="vitualPhone">虚拟号码</param>
        /// <returns></returns>
        public IList<VitualPhone> GetAllPhoneListFromTdialnumberDAL(string actualNo=null,string vitualPhone=null)
        {
            List<VitualPhone> list = new List<VitualPhone>();
            StringBuilder strSql = new StringBuilder();//TODO 此处数据表 上线后需改成正式表结构
            strSql.Append("SELECT [dialnumber],[calleeid] FROM [tdialnumber] where 1=1 ");//dialnumber 真实号码  calleeid虚拟号码
            if (!string.IsNullOrEmpty(actualNo))
            {
                strSql.Append(" and dialnumber=@actualNo"); 
            }
            if (!string.IsNullOrEmpty(vitualPhone))
            {
                strSql.Append(" and calleeid=@vitualPhone");
            }
            DbCommand cmd = dbRemote.GetSqlStringCommand(strSql.ToString());
            dbRemote.AddInParameter(cmd, "actualNo", DbType.String, actualNo);
            dbRemote.AddInParameter(cmd, "vitualPhone", DbType.String, vitualPhone);

            using (IDataReader dataReader = dbRemote.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBindtdialnumber(dataReader));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取本地库 [tdialnumber] 表中 虚拟-真实号码 配对状态 数据的备份
        /// </summary>
        /// <returns></returns>
        public IList<VitualPhone> GetBackUpPhoneListFromTdialnumberDAL(DateTime backTime)
        {
            List<VitualPhone> list = new List<VitualPhone>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [dialnumber],[calleeid] FROM [VPMS.tdialnumberBackUp] where BackUpTime=@backTime");
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(cmd, "backTime", DbType.DateTime, backTime);
            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBindtdialnumber(dataReader));
                }
            }
            return list;
        }

        #region 清空供应商数据表
        /// <summary>
        /// 添加数据到供应商表 tdialnumber,这边暂不做其他操作，只是insert
        /// </summary>
        /// <returns></returns>
        public int ClearTdialnumber()
        {
            try
            {
                StringBuilder strSqlUpdateEShopApply = new StringBuilder();
                strSqlUpdateEShopApply.Append(@"delete from [tdialnumber]");
                DbCommand cmd = dbRemote.GetSqlStringCommand(strSqlUpdateEShopApply.ToString());
                int count = dbRemote.ExecuteNonQuery(cmd);
                Logger.Log.Info("清空表tdialnumber" + count + "条");
                return count;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("tdialnumber表删除新失败", e);
                return -1;
            }
        }
        #endregion

        #region 清空本地表中当前月份申报表的数据 用于系统初始化
        /// <summary>
        /// 清空本地表中当前月份申报表的数据 用于系统初始化
        /// </summary>
        /// <returns>被清除的条数</returns>
        public int ClearEShopApply(DateTime appMonth)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(@"delete  FROM [VTPhoneDB].[dbo].[VPMS.EShopApply]");
                sql.Append(@"where ApplyMonth=@ApplyMonth");
                DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(cmd, "ApplyMonth", DbType.String,CommonHelper.DateForamt(appMonth));
                int count = db.ExecuteNonQuery(cmd);
                Logger.Log.Info("清空本地库EShopApply，申请月份" + CommonHelper.DateForamt(appMonth)+"->" + count + "条");
                return count;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("清空本地库EShopApply失败,申请月份" + CommonHelper.DateForamt(appMonth), e);
                return -1;
            }
        }
        #endregion

        #region 备份供应商数据表
        /// <summary>
        /// 备份供应商数据表tdialnumberBackUp
        /// </summary>
        /// <returns></returns>
        public bool AddTdialnumberToBak(VitualPhone model,DateTime backTime)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into [VPMS.tdialnumberBackUp](");
                strSql.Append("[dialnumber],[calleeid],[BackUpTime],[CounselorId])");
                strSql.Append(" values (");
                strSql.Append("@ActulPhoneNum,@VitualPhone,@backTime,@CounselorId)");
                DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(dbCommand, "VitualPhone", DbType.String, model.VitualPhoneNum);
                db.AddInParameter(dbCommand, "ActulPhoneNum", DbType.String, model.ActulPhoneNum);
                db.AddInParameter(dbCommand, "backTime", DbType.String, backTime.ToString("yyyy-MM-dd HH:mm:ss"));
                db.AddInParameter(dbCommand, "CounselorId", DbType.String,model.CounselorId);
                int count = db.ExecuteNonQuery(dbCommand);
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("添加供应商表数据失败" + model, e);
                return false;
            }
        }
        #endregion


        #region 获取供应商虚拟真实电话备份 时间
        /// <summary>
        ///获取供应商虚拟真实电话备份 时间
        /// </summary>
        /// <returns></returns>
        public List<BackUpInfo> GetBackUpInfoList()
        {
            var res = new List<BackUpInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT COUNT(1) as counts,backuptime
              FROM [VTPhoneDB].[dbo].[VPMS.tdialnumberBackUp] group by backuptime order by backuptime desc");
            DbCommand cmd = db.GetSqlStringCommand(strSql.ToString());
            using (IDataReader dataReader = db.ExecuteReader(cmd))
            {
                while (dataReader.Read())
                {
                    var t = new BackUpInfo(dataReader["backuptime"].ToString(), Int32.Parse(dataReader["counts"].ToString()));
                    res.Add(t);
                }
            }
            return res;
        }
        #endregion

        private VitualPhone ReaderBindtdialnumber(IDataReader dataReader)
        {
            VitualPhone model = new VitualPhone {
                ActulPhoneNum = dataReader["dialnumber"].ToString(),
                VitualPhoneNum = dataReader["calleeid"].ToString()
            };
            return model;
        }
        #endregion

        #region 获取网站套餐信息列表
        public List<PhoneKind> GetPhoneKindList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [Id],[name] FROM [VTPhoneDB].[dbo].[VPMS.PhoneKind]");
            List<PhoneKind> list = new List<PhoneKind>();
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBindPhoneKind(dataReader));
                }
            }
            return list;
        }
        private PhoneKind ReaderBindPhoneKind(IDataReader dataReader)
        {
            PhoneKind model = new PhoneKind(int.Parse(dataReader["Id"].ToString()), dataReader["name"].ToString());
            return model;
        }
        #endregion

        #region 模型映射
        private VitualPhone ReaderBind(IDataReader dataReader)
        {
            VitualPhone model = new VitualPhone
            {
                AssignStatus = (AssignStatus)Convert.ToInt32(dataReader["AssigneSatus"].ToString().Trim()),
                DirectorId = dataReader["DirectorId"].ToString(),
                DirectorName = dataReader["DirectorName"].ToString(),
                Id = Int32.Parse(dataReader["Id"].ToString()),
                VitualPhoneNum = dataReader["VitualPhone"].ToString()
                // modify by sunlf 2013-10-14 11:02:51  此处不再提供电话类型绑定
                //PhoneKind = new PhoneKind(Convert.ToInt32(dataReader["PhoneKindId"].ToString()), dataReader["webName"].ToString()),
            };
            return model;
        }

        private CounselorApplication ReaderBindAPP(IDataReader dataReader)
        {
            HandlerStatus status =HandlerStatus.待处理; //申请处理状态
            object obj = dataReader["Status"];
            if (obj != null && obj != DBNull.Value)
                status = (HandlerStatus)obj;
            var VitualPhone = new VitualPhone {
                VitualPhoneNum = dataReader["VitualPhone"] == DBNull.Value ? "" : dataReader["VitualPhone"].ToString()
            };
            CounselorApplication model = new CounselorApplication
            {
                Id = Convert.ToInt64(dataReader["Id"]),
                ApplicationMonth = dataReader["ApplyMonth"].ToString(),
                CounselorId = dataReader["User_Code"].ToString(),
                CounselorName = dataReader["User_Name"].ToString(),
                DirectorId = dataReader["RCCode"].ToString(),
                DirectorName = dataReader["RC"].ToString(),
                Org_Name = dataReader["Org_Name"].ToString(),
                Mobile = dataReader["Mobile"].ToString(),
                PhoneKind = new PhoneKind(Convert.ToInt32(dataReader["PhoneKindId"].ToString().Trim()), dataReader["Web_Name"].ToString()),
                Status = status,
                VitualPhone = VitualPhone,
                AddDate = dataReader["ModifyDate"].ToString()
            };
            return model;
        }
        #endregion

        #region 添加总监虚拟电话到本地数据库
        /// <summary>
        /// 添加数据到供应商表 tdialnumber,这边暂不做其他操作，只是insert
        /// </summary>
        /// <returns></returns>
        public CommResult AddVitualPhoneDAL(string directorName, string directorId, string vitualPhone, string assigneSatus)
        {
            CommResult res = new CommResult();
            try
            {
                StringBuilder strSql = new StringBuilder();
                //TODO 上线后更改成供应商正式数据表
                strSql.Append("insert into [VPMS.VitualPhone] (");
                strSql.Append("[DirectorName],[DirectorId],[VitualPhone],[AssigneSatus])");
                strSql.Append(" values (");
                strSql.Append("@DirectorName,@DirectorId,@VitualPhone,@AssigneSatus)");
                DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(dbCommand, "DirectorName", DbType.String, directorName);
                db.AddInParameter(dbCommand, "DirectorId", DbType.String, directorId);
                db.AddInParameter(dbCommand, "VitualPhone", DbType.String, vitualPhone);
                db.AddInParameter(dbCommand, "AssigneSatus", DbType.String, assigneSatus);
                int count = db.ExecuteNonQuery(dbCommand);
                if (count == 1)
                {
                    res.Result = true;
                }
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("虚拟号码:" + vitualPhone+"---"+e.Message,e);
                res.Result = false;
                res.Exception = "虚拟号码:" + vitualPhone + "--添加失败--数据库中已存在对应号码" ;
            }
            return res;
        }
        #endregion

        #region 添加虚拟电话操作日志信息
        /// <summary>
        /// 添加虚拟电话操作日志信息
        /// </summary>
        /// <returns></returns>
        public bool AddVitualPhoneLog(VitualPhoneLog model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert [VTPhoneDB].[dbo].[VPMS.VitualPhoneLog](");
                strSql.Append(@"[DirectorId]
                  ,[DirectorName]
                  ,[CounselorId]
                  ,[CounselorName]
                  ,[Mobile]
                  ,[VitualPhone]
                  ,[Mark]
                  ,[Type]
                  )");
                strSql.Append(" values(@DirectorId,@DirectorName,@CounselorId,@CounselorName,@Mobile,@VitualPhone,@Mark,@Type)");
                DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(dbCommand, "DirectorId", DbType.String, model.DirectorId);
                db.AddInParameter(dbCommand, "DirectorName", DbType.String, model.DirectorName);
                db.AddInParameter(dbCommand, "CounselorId", DbType.String, model.CounselorId);
                db.AddInParameter(dbCommand, "CounselorName", DbType.String, model.CounselorName);
                db.AddInParameter(dbCommand, "Mobile", DbType.String, model.Mobile);
                db.AddInParameter(dbCommand, "VitualPhone", DbType.String, model.VitualPhone);
                db.AddInParameter(dbCommand, "Mark", DbType.String, model.Mark);
                db.AddInParameter(dbCommand, "Type", DbType.String, model.Type);
                int count = db.ExecuteNonQuery(dbCommand);
                return count == 1;
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("添加虚拟电话操作日志信息失败" + model, e);
                return false;
            }
        }
        #endregion

        #region 添加总监虚拟电话到本地库
        /// <summary>
        /// 添加总监虚拟电话到本地库
        /// </summary>
        /// <returns></returns>
        public bool AddVitualPhoneBakDAL(DateTime backUpTime, string directorName, string directorId, string vitualPhone,string webName, string mobile, string counselorId, string counselorName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                //TODO 上线后更改成供应商正式数据表
                strSql.Append("insert into [VPMS.VitualPhoneBakUp] (");
                strSql.Append(@"[BackUpTime]
              ,[DirectorName]
              ,[DirectorId]
              ,[VitualPhone]
              ,[WebName]
              ,[Mobile]
              ,[CounselorId]
              ,[CounselorName])");
                strSql.Append(" values (");
                strSql.Append("@BackUpTime,@DirectorName,@DirectorId,@VitualPhone,@WebName,@Mobile,@CounselorId,@CounselorName)");
                DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(dbCommand, "BackUpTime", DbType.Date, backUpTime);
                db.AddInParameter(dbCommand, "DirectorName", DbType.String,directorName);
                db.AddInParameter(dbCommand, "DirectorId", DbType.String, directorId);
                db.AddInParameter(dbCommand, "VitualPhone", DbType.String, vitualPhone);
                db.AddInParameter(dbCommand, "WebName", DbType.String, webName);
                db.AddInParameter(dbCommand, "Mobile", DbType.String, String.IsNullOrEmpty(mobile) ? "" : mobile);
                db.AddInParameter(dbCommand, "CounselorId", DbType.String, String.IsNullOrEmpty(counselorId) ? "" : counselorId);
                db.AddInParameter(dbCommand, "CounselorName", DbType.String, String.IsNullOrEmpty(counselorName) ? "" : counselorName);

                int count = db.ExecuteNonQuery(dbCommand);
                return count == 1;
       
            }
            catch (Exception e)
            {
                Logger.LogDB.Error("添加总监虚拟电话到本地库异常", e);
                return false;
            }
         
        }
        #endregion
    }
}
